﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;

namespace HRM.DAL.Gateway
{
    public class MessageGateway : DBGateway
    {
        public bool SendMessage(Message message)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("insert into tblMessage values(@sender,@sname,@reciver,@rname,@subject,@body,@date,@flag)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@sender", SqlDbType.VarChar).Value = message.Sender.CodeName;
                CommandObj.Parameters.Add("@sname", SqlDbType.VarChar).Value = message.SenderName;
                CommandObj.Parameters.Add("@reciver", SqlDbType.VarChar).Value = message.Recever.CodeName;
                CommandObj.Parameters.Add("@rname", SqlDbType.VarChar).Value = message.ReceverName;
                CommandObj.Parameters.Add("@subject", SqlDbType.NVarChar).Value = message.Subject;
                CommandObj.Parameters.Add("@body", SqlDbType.NVarChar).Value = message.MessageBody;
                CommandObj.Parameters.Add("@date", SqlDbType.NVarChar).Value = message.Date;
                CommandObj.Parameters.Add("@flag", SqlDbType.VarChar).Value = "Unread";
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured While Sending Message");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<Message> ReatriveMessages(Code code)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format(@"select * from tblMessage where ReciverCode='" + code.CodeName + "' order by Date DESC");
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<Message> messages = new List<Message>();
                while (reader.Read())
                {
                    Message message = new Message();
                    message.Id = Convert.ToInt32(reader[0]);
                    Code senderCode = new Code();
                    senderCode.CodeName = reader[1].ToString();
                    message.Sender = senderCode;
                    message.SenderName = reader[2].ToString();
                    Code reciverCode = new Code();
                    reciverCode.CodeName = reader[3].ToString();
                    message.Recever = reciverCode;
                    message.ReceverName = reader[4].ToString();
                    message.Subject = reader[5].ToString();
                    message.MessageBody = reader[6].ToString();
                    DateTime dt = new DateTime();
                    dt = Convert.ToDateTime(reader[7]);
                    message.Date = dt.ToShortDateString();
                    message.Flag = reader[8].ToString();
                    messages.Add(message);
                }
                return messages;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured While Retriving Messages");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public Message ReadMessage(int messageId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format(@"select * from tblMessage where Id='" + messageId + "'");
                SqlDataReader reader = CommandObj.ExecuteReader();
                Message message = new Message();
                while (reader.Read())
                {
                    message.Id = Convert.ToInt32(reader[0]);
                    Code senderCode = new Code();
                    senderCode.CodeName = reader[1].ToString();
                    message.Sender = senderCode;
                    message.SenderName = reader[2].ToString();
                    Code reciverCode = new Code();
                    reciverCode.CodeName = reader[3].ToString();
                    message.Recever = reciverCode;
                    message.ReceverName = reader[4].ToString();
                    message.Subject = reader[5].ToString();
                    message.MessageBody = reader[6].ToString();
                    DateTime dt = new DateTime();
                    dt = Convert.ToDateTime(reader[7]);
                    message.Date = dt.ToShortDateString();
                    message.Flag = reader[8].ToString();
                   
                }
                return message;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured While Retriving Messages");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool DeleteMessage(int messageId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format(@"delete from tblMessage where Id=@id");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@id", SqlDbType.Int).Value = messageId;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured While Deleting Messages");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool ChangeFlag(int id)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("update tblMessage set flag=@flag where Id='"+id+"'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@flag", SqlDbType.VarChar).Value = "Read";
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured While Changing Flag");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public int CountUnRead(Code code)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select count(flag) from tblMessage where flag='Unread' and ReciverCode='"+code.CodeName+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                int count = 0;
                while (reader.Read())
                {
                    count = Convert.ToInt32(reader[0]);
                }
                return count;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}